---
title: "Acala / Karura aUSD Dashboard"
output:
  flexdashboard::flex_dashboard:
    orientation: rows
    vertical_layout: scroll
    social: menu
    source_code: embed

---

```{css custom1, echo=FALSE}
.dataTables_scrollBody {
    max-height: 100% !important;
}
```

```{r global, include=FALSE}
library(knitr)
knitr::opts_chunk$set(
  message = FALSE,
  warning = FALSE,
  comment = "#>"
)

library(ggplot2)
library(ggpubr)
# library(dygraphs)
library(kableExtra)
library(formattable)
library(lubridate)
library(flexdashboard)
library(DT)
library(subscanr)
library(formattable)
library(ghql)
x <- GraphqlClient$new()

# Helper function to concat
`%+%` <- function(a, b) paste0(a, b)

# window <- today() - as.Date("2021-12-31") + 1
window <- 10

k_collateral <- getLoansDailyCollateral_acala_loan("karura", window)
k_current_collateral <- k_collateral[Date == max(Date), .(collateral.id, depositVolumeUSD)]
k_current_collateral <- k_current_collateral[depositVolumeUSD > 0]
k_collateral[, pct := round(depositVolumeUSD / sum(depositVolumeUSD), 3) * 100, by = "Date"]
k_collateral[, labs := pct %+% "%", by = "Date"]

setorder(k_collateral, pct)
k_current_collateral <- k_collateral[Date == max(Date), .(collateral.id, depositVolumeUSD)]
k_current_collateral <- k_collateral[depositVolumeUSD > 0 & Date == max(Date), .(collateral.id, depositVolumeUSD, pct, labs)]
max(k_collateral$Date)

a_collateral <- getLoansDailyCollateral_acala_loan("acala", window)
a_collateral[, pct := round(depositVolumeUSD / sum(depositVolumeUSD), 3) * 100, by = "Date"]
a_collateral[, labs := pct %+% "%", by = "Date"]
setorder(a_collateral, pct)
a_current_collateral <- a_collateral[depositVolumeUSD > 0 & Date == max(Date), .(collateral.id, depositVolumeUSD, pct, labs)]
max(a_collateral$Date)

# both_collateral <- rbind(k_collateral, a_collateral, fill = TRUE)
# both_current_collateral <- both_collateral[Date == max(Date), .(collateral.id, depositVolumeUSD)]
both_collateral <- rbind(k_collateral, a_collateral)
both_collateral[, pct := NULL]
both_collateral[, labs := NULL]
both_collateral[, pct := round(depositVolumeUSD / sum(depositVolumeUSD), 3) * 100]
both_collateral[, labs := pct %+% "%"]
setorder(both_collateral, pct)
both_collateral <- both_collateral[depositVolumeUSD > 0]
both_current_collateral <- both_collateral[depositVolumeUSD > 0 & Date == max(Date), .(collateral.id, depositVolumeUSD, pct, labs)]

# Collaeralization Ratio
k_cratio <- k_collateral[, sum(depositVolumeUSD) / sum(debitVolumeUSD), by = Date] %>%
  setnames("V1", "Karura Collaeralization Ratio")
  
a_cratio <- a_collateral[, sum(depositVolumeUSD) / sum(debitVolumeUSD), by = Date] %>%
  setnames("V1", "Acala Collaeralization Ratio")
  
both_cratio <- both_collateral[, sum(depositVolumeUSD) / sum(debitVolumeUSD), by = Date] %>%
  setnames("V1", "Combined Collaeralization Ratio")

# Stablecoin percentage of collateral
k_pct_stablecoin <- k_collateral[, sum(debitVolumeUSD) / sum(depositVolumeUSD), by = Date] %>%
  setnames("V1", "Combined Stablecoin Percentage of Collaeral")

a_pct_stablecoin <- a_collateral[, sum(debitVolumeUSD) / sum(depositVolumeUSD), by = Date] %>%
  setnames("V1", "Combined Stablecoin Percentage of Collaeral")

both_pct_stablecoin <- both_collateral[, sum(debitVolumeUSD) / sum(depositVolumeUSD), by = Date] %>%
  setnames("V1", "Combined Stablecoin Percentage of Collaeral")

a_params_current <- getLoansCollateralParams_acala_loan("Acala")
k_params_current <- getLoansCollateralParams_acala_loan("Karura")

cols <- c("collateral.id","maximumTotalDebitValue","liquidationRatio","requiredCollateralRatio")
a_params_current[, ..cols]
k_params_current[, ..cols]


a_params <- getLoansCollateralParamsHistory_acala_loan("Acala")
# a_params

k_params <- getLoansCollateralParamsHistory_acala_loan("Karura")

params <- rbind(a_params, k_params)
params[, Date := as.Date(startAt)]
params[, Block := as.numeric(startAtBlockId)]
params[, maximumTotalDebitValue := as.numeric(maximumTotalDebitValue)]
params[, interestRatePerSec := as.numeric(interestRatePerSec)]
params[, interestRateAnnualized := ((interestRatePerSec / 1e18 + 1) ** (365 * 86400) - 1)]
# summary(params$interestRateAnnualized)

# names(params)
# str(params)

params_long <- melt(params, id.vars=c("collateral.id", "Block"), measure.vars=c("maximumTotalDebitValue","interestRateAnnualized","liquidationRatio","liquidationPenalty","requiredCollateralRatio"))
params_long <- unique(params_long)
setorder(params_long, collateral.id, variable, Block)

params_long[, .N, by = collateral.id]


tag <- "KAR"
params_long[collateral.id == tag & Block > 376050]

kar_params <- ggplot(params_long[collateral.id == tag & Block > 376050], aes(Block, value)) +
  geom_line() +
  facet_wrap(vars(variable), scales = "free_y", nrow = 2, strip.position = "top") +
  theme(strip.background = element_blank(), strip.placement = "outside") +
  theme(axis.text.x=element_text(angle = -90, hjust = 0))

#   theme_void() # remove background, grid, numeric labels

# Liquidity Pool TVL ($3.72M) = totalDefiTvl = totalDexTvl + totalDexLockedTvl + homaLocked + stableCoinTvl
kdex <- getDailyPools_acala_dex("karura", window) %>%
  setorder(Date, pair)
adex <- getDailyPools_acala_dex("acala", window) %>%
  setorder(Date, pair)
adaily <- adex[, max(updateAtBlock.id), by = Date] %>%
  setnames("V1", "blockAcala")
kdaily <- kdex[, max(updateAtBlock.id), by = Date] %>%
  setnames("V1", "blockKarura")
dailyTVL <- merge(adaily, kdaily, by = 'Date')

library(reticulate)
# use_python("/opt/homebrew/bin/python3.9")
```

```{python, include=FALSE}
from substrateinterface import SubstrateInterface

import pandas as pd

def getFeePayment(network):
    if network=="Karura":
        url = 'wss://karura.polkawallet.io'
        token = 'KUSD'
    else:
        url = 'wss://acala-rpc-0.aca-api.network'
        token = 'AUSD'
    substrate = SubstrateInterface(url)
    data = []
    for block_id in r.blocks:
        hash = substrate.get_block_hash(0)
        fee = substrate.query(module='TransactionPayment',storage_function='TransactionFeePaid', block_hash = hash)
        outi = {"Block": block_id, 'aUSD': ausd.value / 10**12}
        data.append(outi)
    out = pd.DataFrame(data)
    return out





def getTotalIssuance(network):
    if network=="Karura":
        url = 'wss://karura.polkawallet.io'
        token = 'KUSD'
    else:
        url = 'wss://acala-rpc-0.aca-api.network'
        token = 'AUSD'
    substrate = SubstrateInterface(url)
    data = []
    for block_id in r.blocks:
        hash = substrate.get_block_hash(int(block_id))
        ausd = substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'Token': token}], block_hash = hash)
        outi = {"Block": block_id, 'aUSD': ausd.value / 10**12}
        data.append(outi)
    out = pd.DataFrame(data)
    return out

```



```{r tvl, cache = TRUE, include=FALSE}

  blocks = as.character(dailyTVL$blockKarura)
  kusd = py$getTotalIssuance("Karura")
  
  blocks = as.character(dailyTVL$blockAcala)
  ausd = py$getTotalIssuance("Acala")

  dailyTVL[, blockAcala  := as.numeric(blockAcala)]  
  dailyTVL[, blockKarura := as.numeric(blockKarura)]  
  kusd$Block <- as.numeric(kusd$Block)
  ausd$Block <- as.numeric(ausd$Block)
  
  dailyTVL <- merge(dailyTVL, kusd, by.x = "blockKarura", by.y = "Block") %>%
    setnames("aUSD", "aUSD_Karura")
  dailyTVL <- merge(dailyTVL, ausd, by.x = "blockAcala", by.y = "Block") %>%
    setnames("aUSD", "aUSD_Acala")
  dailyTVL[, aUSD_Total := aUSD_Karura + aUSD_Acala]
  
  # fwrite(x = dailyTVL, file = "aUSD_Issuance.csv")

```

# Charts {.tabset}

Row
----

### Components of Total Value Locked

```{r k_plot1}

dat <- melt(data = dailyTVL[, .(Date, aUSD_Karura, aUSD_Acala)], 
      id.vars = "Date",
      value.name = "aUSD")
dat[, aUSD := aUSD / 1e6]
 
# Stacked
ggplot(dat, aes(fill=variable, y=aUSD, x=Date)) + 
    geom_bar(position="stack", stat="identity") +
    ggtitle(label="Issuance of aUSD") + 
    ylab("aUSD (in millions USD)")

```


Row
----

```{r pie, out.width = '33%'}
# ggpie chart
a_df <- as.data.frame(a_current_collateral)
k_df <- as.data.frame(k_current_collateral)
both_df <- as.data.frame(both_current_collateral)

ggpie(a_df, "pct", label = "labs", fill = "collateral.id", color = "white", lab.font = c(2, "bold", "red"), main = "aUSD collateral on Acala")

ggpie(k_df, "pct", label = "labs", fill = "collateral.id", lab.font = c(2, "bold", "red"), main = "aUSD collateral on Acala")

ggpie(both_df, "pct", label = "labs", fill = "collateral.id", lab.font = c(2, "bold", "red"), main = "aUSD collateral in Aggregate")

```

Row
----

```{r donut, out.width = '33%'}

# df <- data.frame(group = c("A", "B", "C","D"), value = c(1, 21, 26, 52))
# df$labs <- paste0(df$group, " (", df$value, "%)")
# ggdonutchart(df, "value", label = "labs", fill = "group", color = "white")

ggdonutchart(a_df, "pct", label = "labs", fill = "collateral.id", color = "white", lab.font = c(2, "bold", "red"), main = "aUSD collateral on Acala")

ggdonutchart(k_df, "pct", label = "labs", fill = "collateral.id", color = "white", lab.font = c(2, "bold", "red"), main = "aUSD collateral on Karura")

ggdonutchart(both_df, "pct", label = "labs", fill = "collateral.id", color = "white", lab.font = c(2, "bold", "red"), main = "aUSD collateral in Aggregate")

```

Row
----

```{r lollipop, out.width = '33%'}

# lollipop
ggplot(a_current_collateral, aes(x=collateral.id, y=depositVolumeUSD)) +
  geom_segment( aes(x=collateral.id, xend=collateral.id, y=0, yend=depositVolumeUSD)) +
  geom_point( size=5, color="red", fill=alpha("orange", 0.3), alpha=0.7, shape=21, stroke=2) +
  ggtitle("aUSD collateral on Acala", ) +
theme(plot.title = element_text(color="red", size=14, face="plain"),
axis.title.x = element_blank(),
axis.title.y = element_text(color="#993333", size=14, face="plain")
)  

ggplot(k_current_collateral, aes(x=collateral.id, y=depositVolumeUSD)) +
  geom_segment( aes(x=collateral.id, xend=collateral.id, y=0, yend=depositVolumeUSD)) +
  geom_point( size=5, color="red", fill=alpha("orange", 0.3), alpha=0.7, shape=21, stroke=2) +
  ggtitle("aUSD collateral on Karura", ) +
theme(plot.title = element_text(color="red", size=14, face="plain"),
axis.title.x = element_blank(),
axis.title.y = element_text(color="#993333", size=14, face="plain")
)  

ggplot(both_current_collateral, aes(x=collateral.id, y=depositVolumeUSD)) +
  geom_segment( aes(x=collateral.id, xend=collateral.id, y=0, yend=depositVolumeUSD)) +
  geom_point( size=5, color="red", fill=alpha("orange", 0.3), alpha=0.7, shape=21, stroke=2) +
  ggtitle("aUSD collateral in Aggregate", ) +
theme(plot.title = element_text(color="red", size=14, face="plain"),
axis.title.x = element_blank(),
axis.title.y = element_text(color="#993333", size=14, face="plain")
)  

```

# Minting data {.tabset}

Row
----

### aUSD Issuance

```{r ausd}

knitr::kable(dailyTVL, escape = FALSE, format.args = list(big.mark = ",")) %>%
  kable_styling()

```

Row
----

### Sources and Notes

Please click on the *Source Code* link at the top of the document to see the full source code.  Here is a summary:

#### aUSD
- aUDS (or kUSD) obtained from the [py-substrate-interface](https://github.com/polkascan/py-substrate-interface) using function *substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'Token': 'AUSD'}], block_hash = hash)*


# Collateral data {.tabset}

Row
----

### aUSD Collateral

```{r collateal}

k_collateral_wide <- dcast(k_collateral, timestamp ~ collateral.id, value.var = "depositVolumeUSD", fun.aggregate = sum)
k_collateral_wide[, Total := rowSums(k_collateral_wide[, -1])]

a_collateral_wide <- dcast(a_collateral, timestamp ~ collateral.id, value.var = "depositVolumeUSD", fun.aggregate = sum)
a_collateral_wide[, Total := rowSums(a_collateral_wide[, -1])]

both_collateral_wide <- dcast(both_collateral, timestamp ~ collateral.id, value.var = "depositVolumeUSD", fun.aggregate = sum)
both_collateral_wide[, Total := rowSums(both_collateral_wide[, -1])]

knitr::kable(a_collateral_wide, escape = FALSE, format.args = list(big.mark = ","), caption = "aSUD Collateral on Acala") %>%
  kable_styling()

knitr::kable(k_collateral_wide, escape = FALSE, format.args = list(big.mark = ","), caption = "aSUD Collateral on Karura") %>%
  kable_styling()

knitr::kable(both_collateral_wide, escape = FALSE, format.args = list(big.mark = ","), caption = "aSUD Collateral in Aggregate") %>%
  kable_styling()

```